import mysql.connector
from mysql.connector import Error

def update_database():
    connection = None
    try:
        # 连接到MySQL服务器和数据库
        connection = mysql.connector.connect(
            host='ip',
            port=33060,
            user='root',
            password='password',
            database='erp_db'
        )
        
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 创建会计科目表
            create_accounting_subject_table = """
            CREATE TABLE IF NOT EXISTS accounting_subject (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                code VARCHAR(20) NOT NULL COMMENT '科目编码',
                name VARCHAR(100) NOT NULL,
                parent_id BIGINT DEFAULT NULL,
                subject_type ENUM('ASSET','LIABILITY','EQUITY','INCOME','EXPENSE') NOT NULL,
                is_leaf TINYINT(1) DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                is_deleted TINYINT(1) DEFAULT 0,
                UNIQUE KEY uk_code (code),
                KEY idx_parent (parent_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会计科目'
            """
            cursor.execute(create_accounting_subject_table)
            print("会计科目表创建成功")
            
            # 创建凭证表
            create_voucher_table = """
            CREATE TABLE IF NOT EXISTS voucher (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                voucher_no VARCHAR(50) NOT NULL COMMENT '凭证号',
                period DATE NOT NULL COMMENT '会计期间 YYYY-MM',
                summary VARCHAR(200) NOT NULL,
                status ENUM('DRAFT','POSTED','VOID') DEFAULT 'DRAFT',
                created_by BIGINT NOT NULL,
                posted_at DATETIME DEFAULT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                is_deleted TINYINT(1) DEFAULT 0,
                UNIQUE KEY uk_voucher_no (voucher_no),
                KEY idx_period (period),
                KEY idx_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """
            cursor.execute(create_voucher_table)
            print("凭证表创建成功")
            
            # 创建凭证分录表
            create_voucher_entry_table = """
            CREATE TABLE IF NOT EXISTS voucher_entry (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                voucher_id BIGINT NOT NULL,
                subject_id BIGINT NOT NULL,
                debit_amount DECIMAL(15,2) DEFAULT 0.00,
                credit_amount DECIMAL(15,2) DEFAULT 0.00,
                currency CHAR(3) DEFAULT 'CNY',
                description VARCHAR(200) DEFAULT NULL,
                KEY idx_voucher_id (voucher_id),
                KEY idx_subject_id (subject_id),
                CONSTRAINT fk_voucher_entry_voucher FOREIGN KEY (voucher_id) REFERENCES voucher (id) ON DELETE CASCADE,
                CONSTRAINT fk_voucher_entry_subject FOREIGN KEY (subject_id) REFERENCES accounting_subject (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """
            cursor.execute(create_voucher_entry_table)
            print("凭证分录表创建成功")
            
    except Error as e:
        print(f"更新数据库时出错: {e}")
        
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL连接已关闭")

if __name__ == "__main__":
    update_database()